import * as path from 'path';
import * as fs from 'fs';
import Papa, { ParseResult } from 'papaparse';
import { getDbInstance, SqliteDatabase } from './sqlite-database';
import { sanitizeForTableName, sanitizeForColumnName } from '@/lib/utils';

async function parseCsvFile(filePath: string): Promise<{ headers: string[], rows: any[][] }> {
  const csvFile = fs.readFileSync(filePath, 'utf8');
  return new Promise((resolve, reject) => {
    Papa.parse(csvFile, {
      header: true,
      skipEmptyLines: true,
      complete: (results: ParseResult<any>) => {
        if (results.errors.length > 0) {
          console.error(`[CSV Parser] Errors parsing ${filePath}:`, results.errors);
          reject(new Error(`Error parsing CSV file: ${results.errors.map((e: any) => e.message).join(', ')}`));
          return;
        }
        const headers = results.meta.fields || [];
        const rows = results.data.map((row: any) => headers.map((header: string) => row[header]));
        resolve({ headers, rows });
      },
      error: (error: Error) => {
        console.error(`[CSV Parser] Critical error reading ${filePath}:`, error);
        reject(error);
      }
    });
  });
}

export async function importRawCsvsFromDir(directoryPath: string) {
  console.log(`[Raw CSV Importer] Starting raw import from directory: ${directoryPath}`);
  const db: SqliteDatabase = getDbInstance();
  const sourceDirectory = path.join(process.cwd(), 'excel', directoryPath);
  
  try {
    const files = fs.readdirSync(sourceDirectory).filter(file => file.endsWith('.csv'));
    const results = [];

    for (const fileName of files) {
      const filePath = path.join(sourceDirectory, fileName);
      const { headers, rows } = await parseCsvFile(filePath);

      if (!headers || headers.length === 0) {
        console.log(`[Raw CSV Importer] Skipping empty file: ${fileName}`);
        continue;
      }
      
      const tableName = sanitizeForTableName(`raw_${path.parse(fileName).name}`);
      const columns = headers.map(h => sanitizeForColumnName(h || 'Unnamed_Column'));
      
      console.log(`[Raw CSV Importer] Processing file: ${fileName} -> table: ${tableName}`);

      db.exec(`DROP TABLE IF EXISTS ${tableName}`);
      console.log(`[Raw CSV Importer] Dropped table if exists: ${tableName}`);

      const createTableQuery = `CREATE TABLE ${tableName} (${columns.map(c => `'${c}' TEXT`).join(', ')})`;
      db.exec(createTableQuery);
      console.log(`[Raw CSV Importer] Created table: ${tableName}`);
      
      const stmt = db.prepare(`INSERT INTO ${tableName} (${columns.map(c => `'${c}'`).join(', ')}) VALUES (${columns.map(() => '?').join(', ')})`);
      const insertMany = db.transaction((dataRows: any[][]) => {
        for (const row of dataRows) {
          // Ensure row has the same number of columns as headers
          const trimmedRow = columns.map((_, i) => row[i]);
          stmt.run(trimmedRow.map(cell => cell !== null && cell !== undefined ? String(cell) : null));
        }
      });
      
      insertMany(rows);

      console.log(`[Raw CSV Importer] Inserted ${rows.length} rows into ${tableName}`);
      
      results.push({
        fileName: fileName,
        tableName: tableName,
        rowCount: rows.length,
        columns: columns
      });
    }
    
    console.log('[Raw CSV Importer] Raw CSV import finished successfully.');
    return { 
      message: '原始CSV参数从目录导入成功', 
      details: results 
    };

  } catch (error: any) {
    console.error('[Raw CSV Importer] Error during raw CSV import:', error);
    throw new Error(`无法导入原始CSV文件: ${error.message}`);
  }
} 